package com.gjzt.business.publish.service;

import java.io.BufferedInputStream;
import java.io.BufferedOutputStream;
import java.io.FileInputStream;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.HashMap;
import java.util.List;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.gjzt.business.media.inter.MediaTypeMapper;
import com.gjzt.business.media.model.MediaType;
import net.sf.json.JSONArray;
import net.sf.json.JSONObject;

import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.eastdigit.servlet.ReqBean;
import com.eastdigit.system.model.BusinessException;
import com.eastdigit.system.model.Constants;
import com.eastdigit.system.model.SqlBuilder;
import com.eastdigit.system.service.BaseService;
import com.eastdigit.util.DateUtil;
import com.eastdigit.util.StringUtil;
import com.gjzt.business.media.inter.MediaFileMapper;
import com.gjzt.business.media.service.MediaFileService;
import com.gjzt.business.publish.inter.MakeBillDetailMapper;
import com.gjzt.business.publish.inter.MakeBillMapper;
import com.gjzt.business.publish.inter.PublishBillDetailMapper;
import com.gjzt.business.publish.inter.PublishBillMapper;
import com.gjzt.business.publish.model.MakeBill;
import com.gjzt.business.publish.model.MakeBillDetail;
import com.gjzt.business.publish.model.PublishBill;
import com.gjzt.business.publish.model.PublishBillDetail;
import com.gjzt.business.utils.Constant;

@Service
public class PublishBillService extends BaseService {

    @Autowired
    private PublishBillMapper publishBillMapper;
    @Autowired
    private PublishBillDetailMapper publishBillDetailMapper;
    @Autowired
    private MediaFileService mediaFileService;
    @Autowired
    private MediaFileMapper mediaFileMapper;
    @Autowired
    private MakeBillMapper makeBillMapper;
    @Autowired
    private MakeBillDetailMapper makeBillDetailMapper;

    @Autowired
    private MediaTypeMapper mediaTypeMapper;

    /**
     * 查询
     * 
     * @param reqBean
     * @return
     */
    public JSONObject findAdPublishBill(ReqBean reqBean) {
        String quickSearch = reqBean.getInfo("quickSearch");
        SqlBuilder sqlBuilder = null;
        // 是快速查询
        if (StringUtils.isNotBlank(quickSearch) && quickSearch.equals(Constant.QUICK_SEARCH_TRUE)) {
            sqlBuilder = new SqlBuilder(
                    "SELECT a.BILL_ID,a.BILL_NO,a.BILL_TITLE,a.APPLY_TIME,a.BILL_TYPE,a.AUDIT_TIME,a.STATUS publishBillStatus,"
                            + "a.AUDITOR,a.COMMENT,ad.* FROM `AD_PUBLISH_BILL` a left join AD_CUSTOMER ad "
                            + "ON ad.CUSTOMER_ID = a.CUSTOMER_ID where 1=1").addWhereLike2("a.BILL_NO",
                    reqBean.getInfo("quickSearchName"));
        } else {
            sqlBuilder = new SqlBuilder(
                    "SELECT a.BILL_ID,a.BILL_NO,a.BILL_TITLE,a.APPLY_TIME,a.BILL_TYPE,a.content,a.PUBLISH_TIME ,a.PUBLISH_OFF_TIME, a.AUDIT_TIME,a.STATUS publishBillStatus,"
                            + "a.AUDITOR,a.COMMENT,ad.* FROM `AD_PUBLISH_BILL` a left join AD_CUSTOMER ad "
                            + "ON ad.CUSTOMER_ID = a.CUSTOMER_ID where 1=1 ")
                    .addWhere("a.BILL_NO", reqBean.getInfo("billNo")).addWhere("a.STATUS", reqBean.getInfo("status"))
                    .addWhereLike2("ad.CUSTOMER_NAME", reqBean.getInfo("customerName"))
                    .addWhereLike2("a.CONTACTS_PHONE", reqBean.getInfo("contactsPhone"));
        }
        sqlBuilder.appendSql(" ORDER BY a.APPLY_TIME desc");
        return this.queryTable(sqlBuilder, reqBean.getPageSize(), reqBean.getPageIndex());
    }

    public PublishBill getPublishBill(String billId) {
        return publishBillMapper.selectByPrimaryKey(billId);
    }

    /**
     * 新增
     * 
     * @param reqBean
     */
    public void addAdPublishBill(ReqBean reqBean) {
        PublishBill publishBill = getAdPublishBill(reqBean);
        publishBill.setBillId(intIncrementId("AD_PUBLISH_BILL", "BILL_ID"));
        publishBill.setStatus(Constant.PUBLISH_BILL_STATUS_STAFF); // 未提交
        publishBill.setMakeTime(new Date());
        publishBill.setModifyTime(new Date());
        publishBillMapper.insert(publishBill);
    }

    /**
     * 修改
     * 
     * @param reqBean
     */
    public void updateAdPublishBill(ReqBean reqBean) {
        String billId = reqBean.getInfo("billId");
        if (StringUtil.isEmpty(billId)) {
            throw new BusinessException("获取主键失败");
        }
        PublishBill publishBill = getAdPublishBill(reqBean);
        publishBillMapper.updateByPrimaryKeySelective(publishBill);
    }

    /**
     * 删除
     * 
     * @param reqBean
     */
    public void removeAdPublishBill(ReqBean reqBean) {
        String billId = reqBean.getInfo("billId");
        if (StringUtil.isEmpty(billId)) {
            throw new BusinessException("获取主键失败");
        }
        publishBillMapper.deleteByPrimaryKey(billId);
        // 删除明细
        publishBillDetailMapper.deleteByBillId(billId);
        // 删除上刊单对应的资料、样刊
        mediaFileMapper.deleteBySourceTypeAndDataId(Constant.MEDIAFILE_SOURCE_TYPE_PUBLISH_FILE, billId);// 资料
        mediaFileMapper.deleteBySourceTypeAndDataId(Constant.MEDIAFILE_SOURCE_TYPE_PUBLISH_IMG, billId);// 样刊
    }

    /**
     * 从表单获取对象
     * 
     * @param reqBean
     * @return
     */
    private PublishBill getAdPublishBill(ReqBean reqBean) {
        PublishBill publishBill = new PublishBill();
        publishBill.setBillId(reqBean.getInfo("billId"));
        publishBill.setBillNo(reqBean.getInfo("billNo"));
        publishBill.setBillType(reqBean.getInfo("billType"));
        publishBill.setStatus(reqBean.getInfo("publishBillStatus"));
        publishBill.setCustomerId(reqBean.getInfo("customerId"));
        publishBill.setApplyTime(DateUtil.parseDateTime(reqBean.getInfo("applyTime")));
        publishBill.setAuditor(reqBean.getInfo("auditor"));
        publishBill.setComment(reqBean.getInfo("comment"));
        publishBill.setAuditTime(DateUtil.parseDateTime(reqBean.getInfo("auditTime")));
        publishBill.setOperator(reqBean.getUserId());
        publishBill.setMakeTime(DateUtil.parseDateTime(reqBean.getInfo("makeTime")));
        publishBill.setModifyTime(DateUtil.parseDateTime(reqBean.getInfo("modifyTime")));
        publishBill.setRemark(reqBean.getInfo("remark"));
        return publishBill;
    }

    /**
     * 添加上刊单
     * 
     * @param reqBean
     */
    public JSONObject saveBillDetails(ReqBean reqBean) {

        String customerId = reqBean.getInfo("customerId");
        String billNo = reqBean.getInfo("billNo");
        String billTitle = reqBean.getInfo("billTitle");
        String billType = reqBean.getInfo("billType");
        String busResourcesStr = reqBean.getInfo("busResources");
        String stopResourcesStr = reqBean.getInfo("stopResources");
        String billId = reqBean.getInfo("billId");
        String billContent = reqBean.getInfo("billContent");
        String billPublishTime = reqBean.getInfo("billPublishTime");
        String billPublishOffTime = reqBean.getInfo("billPublishOffTime");
        String mediaId = reqBean.getInfo("mediaId");

        MediaType mediaType = mediaTypeMapper.selectByPrimaryKey(mediaId);
        /*
         * if ((StringUtils.isBlank(busResourcesStr) ||
         * "[]".endsWith(busResourcesStr)) &&
         * (StringUtils.isBlank(stopResourcesStr) ||
         * "[]".endsWith(stopResourcesStr))) { throw new
         * BusinessException("上刊单数据为空!"); }
         */

        JSONArray busResourcesArr = JSONArray.fromObject(busResourcesStr);
        JSONArray stopResourcesArr = JSONArray.fromObject(stopResourcesStr);

        PublishBill publishBill = null;
        if (StringUtils.isNoneBlank(billId)) {
            publishBill = publishBillMapper.selectByPrimaryKey(billId);

            publishBill.setBillNo(billNo);
            publishBill.setBillTitle(billTitle);
            publishBill.setBillType(billType);
            publishBill.setContent(billContent);
            publishBill.setPublishTime(DateUtil.parse(billPublishTime));
            publishBill.setPublishOffTime(DateUtil.parse(billPublishOffTime));

            publishBill.setModifyTime(new Date());
            publishBill.setOperator(reqBean.getUserId());
            publishBill.setRemark(reqBean.getInfo("remark"));
        } else {
            publishBill = new PublishBill();
            publishBill.setBillId(intIncrementId("AD_PUBLISH_BILL", "BILL_ID"));
            publishBill.setStatus(Constant.PUBLISH_BILL_STATUS_STAFF);
            publishBill.setCustomerId(customerId);

            publishBill.setBillNo(billNo);
            publishBill.setBillTitle(billTitle);
            publishBill.setBillType(billType);
            publishBill.setContent(billContent);
            publishBill.setPublishTime(DateUtil.parse(billPublishTime));
            publishBill.setPublishOffTime(DateUtil.parse(billPublishOffTime));

            publishBill.setOperator(reqBean.getUserId());
            publishBill.setMakeTime(new Date());
            publishBill.setModifyTime(publishBill.getMakeTime());
            publishBill.setRemark(reqBean.getInfo("remark"));
        }

        JSONObject validateJson = new JSONObject();
        validateJson.put("flag", true);

        List<PublishBillDetail> detailList = new ArrayList<PublishBillDetail>();
        // 上刊单明细
        // 车辆
        if (busResourcesArr != null && busResourcesArr.size() > 0) {
            for (int i = 0; i < busResourcesArr.size(); i++) {
                JSONObject json = busResourcesArr.getJSONObject(i);
                JSONObject data = json.getJSONObject("value");
                String resourceId = data.getString("resourceId");
                if (resourceId.contains("#")) {
                    resourceId = resourceId.split("#")[0];
                }

                // 期限是否在合同时间内
                HashMap<String, JSONObject> dateResourceIdMap = new HashMap<String, JSONObject>();
                validateJson = contractValidate(resourceId, billPublishTime, billPublishOffTime, customerId,
                        dateResourceIdMap);
                if (validateJson.getBoolean("flag")) { // 合同校验通过
                    if (Constant.MEDIA_MODEL_TYPE_SINGLE.equals(mediaType.getModelType())) { // 单一媒体类型
                        validateJson = publishBillValidate(resourceId, billPublishTime, billPublishOffTime, customerId,
                                dateResourceIdMap, publishBill.getBillId());
                        if (validateJson.getBoolean("flag")) { // 上刊单校验通过
                            PublishBillDetail details = new PublishBillDetail();
                            details.setBillId(publishBill.getBillId());
                            details.setResourceId(resourceId);
                            detailList.add(details);
                        }
                    } else { // 综合性媒体类型
                        PublishBillDetail details = new PublishBillDetail();
                        details.setBillId(publishBill.getBillId());
                        details.setResourceId(resourceId);
                        detailList.add(details);
                    }
                } else {
                    break;
                }
            }
        }

        // 站点
        if (stopResourcesArr != null && stopResourcesArr.size() > 0) {
            for (int i = 0; i < stopResourcesArr.size(); i++) {
                JSONObject json = stopResourcesArr.getJSONObject(i);
                JSONObject data = json.getJSONObject("value");
                String resourceId = data.getString("resourceId");
                if (resourceId.contains("#")) {
                    resourceId = resourceId.split("#")[0];
                }
                // 期限是否在合同时间内
                HashMap<String, JSONObject> dateResourceIdMap = new HashMap<String, JSONObject>();
                validateJson = contractValidate(resourceId, billPublishTime, billPublishOffTime, customerId,
                        dateResourceIdMap);
                if (validateJson.getBoolean("flag")) { //合同校验通过
                    if (Constant.MEDIA_MODEL_TYPE_SINGLE.equals(mediaType.getModelType())) { // 单一媒体类型
                        validateJson = publishBillValidate(resourceId, billPublishTime, billPublishOffTime, customerId,
                                dateResourceIdMap, publishBill.getBillId());
                        if (validateJson.getBoolean("flag")) {
                            // 插入明细
                            PublishBillDetail details = new PublishBillDetail();
                            details.setBillId(publishBill.getBillId());
                            details.setResourceId(resourceId);
                            detailList.add(details);
                        }
                    } else { // 综合性媒体类型
                        PublishBillDetail details = new PublishBillDetail();
                        details.setBillId(publishBill.getBillId());
                        details.setResourceId(resourceId);
                        detailList.add(details);
                    }
                }
            }
        }

        if (validateJson.getBoolean("flag")) {// 明细验证通过
            if (StringUtils.isNoneBlank(billId)) {// 修改
                publishBillDetailMapper.deleteByBillId(billId);// 删除上刊单详情
                mediaFileMapper.deleteBySourceTypeAndDataId(Constant.MEDIAFILE_SOURCE_TYPE_PUBLISH_FILE, billId);// 资料
                mediaFileMapper.deleteBySourceTypeAndDataId(Constant.MEDIAFILE_SOURCE_TYPE_PUBLISH_IMG, billId);// 样刊
                publishBillMapper.updateByPrimaryKeySelective(publishBill);
            } else {// 新增
                publishBillMapper.insert(publishBill);
            }
            // 插入明细
            for (int i = 0; i < detailList.size(); i++) {
                PublishBillDetail details = detailList.get(i);
                details.setId(intIncrementId("AD_PUBLISH_BILL_DETAIL", "ID"));
                publishBillDetailMapper.insert(details);
            }

            // 插入上刊单对应的资料文件
            JSONArray filesArray = JSONArray.fromObject(reqBean.getInfoObj("bidFiles"));
            JSONObject fileDataJson = mediaFileService.getDataJson(Constant.MEDIAFILE_SOURCE_TYPE_PUBLISH_FILE,
                    publishBill.getBillId(), filesArray);
            if (filesArray.size() > 0 && fileDataJson != null) {
                mediaFileService.addMediaFile(fileDataJson);
            }

            // 插入上刊单对应的样刊
            JSONArray imgsArray = JSONArray.fromObject(reqBean.getInfoObj("bidImgs"));
            JSONObject imgDataJson = mediaFileService.getDataJson(Constant.MEDIAFILE_SOURCE_TYPE_PUBLISH_IMG,
                    publishBill.getBillId(), imgsArray);
            if (imgsArray.size() > 0 && imgDataJson != null) {
                mediaFileService.addMediaFile(imgDataJson);
            }
        }
        return validateJson;
    }

    private JSONObject publishBillValidate(String resourceId, String billStartDate, String billEndDate,
            String customerId, HashMap<String, JSONObject> dateResourceIdMap, String billId) {
        JSONObject result = new JSONObject();
        result.put("flag", true);
        String publishSql = "select r.BILL_ID,r.RESOURCE_ID,apb.BILL_TITLE,apb.PUBLISH_TIME,apb.PUBLISH_OFF_TIME,apb.CUSTOMER_ID "
                + " from AD_PUBLISH_BILL_DETAIL r  LEFT JOIN AD_PUBLISH_BILL apb on apb.BILL_ID=r.BILL_ID "
                + " where r.RESOURCE_ID='" + resourceId + "' and ( apb.PUBLISH_TIME<='" + billEndDate
                + "' and apb.PUBLISH_OFF_TIME>='" + billStartDate + "')";
        if (StringUtils.isNoneBlank(billId)) {// 除去自己的上刊时间
            publishSql = publishSql + " and r.BILL_ID <> '" + billId + "'";
        }
        if (StringUtils.isNoneBlank(customerId)) {
            publishSql = publishSql + " and apb.CUSTOMER_ID = '" + customerId + "'";
        }
        JSONArray publishedArray = this.query(publishSql);

        if (publishedArray != null && publishedArray.size() > 0) {
            // throw new BusinessException("上刊起止时间冲突或者不在可用范围内!");
            System.out.println("上刊起止时间冲突或者不在可用范围内!");
            result.put("flag", false);
            result.put("msg", "上刊起止时间冲突或者不在可用范围内!");
        } else {
            Date startDate = DateUtil.parse(billStartDate);
            Date endDate = DateUtil.parse(billEndDate);
            JSONObject resouceInfo = new JSONObject();
            resouceInfo.put("status", Constant.SCHEDULE_CSS_TO_PUBLISHED);
            Calendar startCalendar = Calendar.getInstance();
            startCalendar.setTime(startDate);
            Calendar endCalendar = Calendar.getInstance();
            endCalendar.setTime(endDate);

            while (endCalendar.after(startCalendar) || endCalendar.equals(startCalendar)) {
                String dateStr = DateUtil.formatDate(startCalendar.getTime());// 日期
                dateResourceIdMap.put(dateStr, resouceInfo);
                startCalendar.add(Calendar.DATE, 1);
            }
        }
        return result;
    }

    private JSONObject contractValidate(String resourceId, String billStartDate, String billEndDate, String customerId,
            HashMap<String, JSONObject> dateResourceIdMap) {
        JSONObject result = new JSONObject();
        result.put("flag", true);
        String contractSql = "select  r.*,ac.CUSTOMER_ID  from AD_CONTRACT_RESOURCE r "
                + " LEFT JOIN AD_CONTRACT ac   on r.CONTRACT_ID=ac.CONTRACT_ID  where r.RESOURCE_ID='" + resourceId
                + "' and ( r.START_DATE<='" + billEndDate + "' and r.END_DATE>='" + billStartDate
                + "') and ac.CUSTOMER_ID=" + customerId;
        JSONArray contractArray = this.query(contractSql);

        if (contractArray != null && contractArray.size() > 0) {
            for (int j = 0; j < contractArray.size(); j++) {
                JSONObject contractJson = contractArray.getJSONObject(j);
                Date startDate = DateUtil.parse(contractJson.getString("startDate"));
                Date endDate = DateUtil.parse(contractJson.getString("endDate"));
                JSONObject resouceInfo = new JSONObject();
                resouceInfo.put("status", Constant.SCHEDULE_CSS_WORKOFF);
                Calendar startCalendar = Calendar.getInstance();
                startCalendar.setTime(startDate);
                Calendar endCalendar = Calendar.getInstance();
                endCalendar.setTime(endDate);

                while (endCalendar.after(startCalendar) || endCalendar.equals(startCalendar)) {
                    String dateStr = DateUtil.formatDate(startCalendar.getTime());// 日期
                    dateResourceIdMap.put(dateStr, resouceInfo);
                    startCalendar.add(Calendar.DATE, 1);
                }
            }
        } else {
            System.out.println("上刊起止时间不在合同范围内!");
            result.put("flag", false);
            result.put("msg", "上刊起止时间不在合同范围内!");
            // throw new BusinessException("上刊起止时间不在合同范围内!");
        }
        return result;
    }

    public void downLoadFile(ReqBean reqBean, HttpServletRequest request, HttpServletResponse response)
            throws Exception {
        String url = reqBean.getInfo("publishFile");
        String filePath = Constants.ATTACHMENT_PATH + "/" + url;
        response.reset();
        response.setContentType("application/x-msdownload");
        // String fileName = url.substring(url.lastIndexOf("/") + 1,
        // url.length());
        String fileName = mediaFileService.getMediaFileNameByFilePath(url);
        response.addHeader("Content-Disposition", "attachment; filename="
                + new String(fileName.getBytes("utf-8"), "ISO8859-1"));
        BufferedInputStream bis = null;
        BufferedOutputStream bos = null;
        byte[] buff = new byte[2048];

        try {
            bis = new BufferedInputStream(new FileInputStream(filePath));
            bos = new BufferedOutputStream(response.getOutputStream());
            int bytesRead;
            while (-1 != (bytesRead = bis.read(buff, 0, buff.length))) {
                bos.write(buff, 0, bytesRead);
            }
            bis.close();
            bos.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 查询上刊单明细
     * 
     * @param reqBean
     * @return
     */
    public JSONObject queryBillDetails(String billId) {
        JSONObject result = new JSONObject();
        if (StringUtils.isBlank(billId)) {
            throw new BusinessException("缺少参数!");
        }
        String sql = "select a.ID,a.BILL_ID,apb.BILL_TITLE,apb.CONTENT,a.PUBLISH_FILE,apb.PUBLISH_TIME billStartDate,apb.PUBLISH_OFF_TIME billEndDate,mr.*,"
                + "mt.MEDIA_NAME,s.STOP_NAME,s.ROAD_NAME,s.DIRECTION,b.VEHICLE_NO,b.PLATE_NO,b.LINE_NO,b.COMPANY_ID,b.COMPANY_NAME,b.BUS_FLOOR "
                + "FROM AD_PUBLISH_BILL_DETAIL a "
                + "LEFT JOIN AD_PUBLISH_BILL apb on apb.BILL_ID = a.BILL_ID "
                + "LEFT JOIN MEDIA_RESOURCE mr ON mr.RESOURCE_ID = a.RESOURCE_ID "
                + "LEFT JOIN MEDIA_TYPE mt ON mt.MEDIA_ID = mr.MEDIA_TYPE "
                + "LEFT JOIN MEDIA_STOP s ON mr.STOP_ID = s.STOP_ID "
                + "LEFT JOIN MEDIA_BUS b ON mr.BUS_ID = b.BUS_ID where a.BILL_ID = '" + billId + "'";
        JSONArray array = query(sql);

        // 查询上刊单对应的文件
        // 资料文件
        JSONArray fileArray = mediaFileService.getMediaFileArrayBySourceTypeAndDataId(
                Constant.MEDIAFILE_SOURCE_TYPE_PUBLISH_FILE, billId, Constant.DATA_STATUS_TRUE);
        // 样刊
        JSONArray imgArray = mediaFileService.getMediaFileArrayBySourceTypeAndDataId(
                Constant.MEDIAFILE_SOURCE_TYPE_PUBLISH_IMG, billId, Constant.DATA_STATUS_TRUE);

        result.put("fileArray", fileArray);
        result.put("imgArray", imgArray);
        result.put("array", array);
        return result;
    }

    public void createMmakeBill(String publishBillId) {
        PublishBill publishBill = publishBillMapper.selectByPrimaryKey(publishBillId);
        JSONObject billInfos = this.queryBillDetails(publishBillId);
        JSONArray billDetls = billInfos.getJSONArray("array");
        MakeBill makeBill = new MakeBill();
        makeBill.setMakeBillId(intIncrementId("AD_PUBLISH_MAKE_BILL", "MAKE_BILL_ID"));
        makeBill.setBillId(publishBillId);
        makeBill.setBillNo("Z" + publishBill.getBillNo());
        makeBill.setBillStatus(Constant.PUBLISH_MAKE_BILL_STATUS_NOT_SEND);
        makeBillMapper.insert(makeBill);
        for (int i = 0; i < billDetls.size(); i++) {
            JSONObject detl = billDetls.getJSONObject(i);
            MakeBillDetail makeBillDetail = new MakeBillDetail();
            makeBillDetail.setMakeBillId(makeBill.getMakeBillId());
            makeBillDetail.setPublishDetailId(detl.getString("id"));
            makeBillDetail.setStatus(Constant.PUBLISH_MAKE_BILL_DETAIL_STATUS_NOT_COMPLETE);
            makeBillDetailMapper.insert(makeBillDetail);
        }
    }

    /**
     * 制作单查询
     * 
     * @param reqBean
     * @return
     */
    public JSONObject queryMakeBill(ReqBean reqBean) {
        String sql = "SELECT a.*,pb.BILL_TITLE,pb.BILL_TYPE,pb.AUDIT_TIME,c.* FROM `AD_PUBLISH_MAKE_BILL` a "
                + "LEFT JOIN AD_PUBLISH_BILL pb ON pb.BILL_ID = a.BILL_ID LEFT JOIN AD_CUSTOMER c ON c.CUSTOMER_ID = pb.CUSTOMER_ID";
        SqlBuilder sqlBuilder = new SqlBuilder(sql).addWhereLike2("ad.CUSTOMER_NAME", reqBean.getInfo("customerName"))
                .addWhereLike2("a.BILL_NO", reqBean.getInfo("billNo"));
        sqlBuilder.appendSql(" ORDER BY pb.APPLY_TIME DESC");
        return queryTable(sqlBuilder, reqBean.getPageSize(), reqBean.getPageIndex());
    }

    public JSONArray queryMakeBills(String billId) {
        List<MakeBill> mkBills = makeBillMapper.selectByBillId(billId);
        JSONArray array = new JSONArray();
        if (mkBills != null && !mkBills.isEmpty()) {
            for (MakeBill mb : mkBills) {
                JSONObject JSONmb = mb.toJsonObject();
                JSONmb.put("detls", queryMakeBillDetails(mb.getMakeBillId()));
                array.add(JSONmb);
            }
        }
        return array;
    }

    public JSONArray queryMakeBillDetails(String makeBillId) {
        if (StringUtils.isBlank(makeBillId)) {
            throw new BusinessException("缺少参数!");
        }
        String sql = "select a.MAKE_BILL_ID,a.STATUS makeBillDetailStatus ,a.REMARK makeBillDetailRemark,a.COMPLETE_TIME,"
                + "a.PUBLISH_DETAIL_ID,mr.*,mt.MEDIA_NAME,s.STOP_NAME,s.ROAD_NAME,s.DIRECTION,b.VEHICLE_NO,b.PLATE_NO,b.LINE_NO,"
                + "b.COMPANY_ID,b.COMPANY_NAME,"
                + "b.BUS_FLOOR,pb.BILL_TITLE,pb.PUBLISH_TIME,pb.PUBLISH_OFF_TIME,pbd.PUBLISH_FILE,pbd.CONTENT,pb.BILL_TYPE "
                + "FROM AD_PUBLISH_MAKE_BILL_DETAIL a LEFT JOIN AD_PUBLISH_BILL_DETAIL pbd ON a.PUBLISH_DETAIL_ID = pbd.ID "
                + "LEFT JOIN AD_PUBLISH_BILL pb ON pb.BILL_ID = pbd.BILL_ID "
                + "LEFT JOIN MEDIA_RESOURCE mr ON mr.RESOURCE_ID = pbd.RESOURCE_ID "
                + "LEFT JOIN MEDIA_TYPE mt ON mt.MEDIA_ID = mr.MEDIA_TYPE "
                + "LEFT JOIN MEDIA_STOP s ON mr.STOP_ID = s.STOP_ID "
                + "LEFT JOIN MEDIA_BUS b ON mr.BUS_ID = b.BUS_ID where 1=1 ";
        SqlBuilder sqlBuilder = new SqlBuilder(sql).addWhere("a.MAKE_BILL_ID", makeBillId);
        JSONArray makeBillDetailsArray = this.query(sqlBuilder);

        // 将完工照图样放入记录中
        for (int i = 0; i < makeBillDetailsArray.size(); i++) {
            JSONObject o = makeBillDetailsArray.getJSONObject(i);
            String publishDetailId = o.getString("publishDetailId");
            o.put("imgs", mediaFileService.getMediaFileArrayBySourceTypeAndDataId(
                    Constant.MEDIAFILE_SOURCE_TYPE_FINISHED, publishDetailId, Constant.DATA_STATUS_TRUE));
        }
        return makeBillDetailsArray;// 最终数据
    }

    public JSONArray busOrStopHead(ReqBean reqBean) {
        String billType = reqBean.getInfo("billType");
        if (StringUtils.isBlank(billType)) {
            throw new BusinessException("缺少参数!");
        }
        JSONArray returnJson = new JSONArray();
        JSONObject headJson = new JSONObject();
        if (Constant.PUBLISH_BILL_TYPE_BUS.equals(billType)) {// 车辆
            headJson.put("col", "vehicleNo");
            headJson.put("name", "自编号");
            returnJson.add(headJson);
            headJson.put("col", "plateNo");
            headJson.put("name", "车牌");
            returnJson.add(headJson);
            headJson.put("col", "lineNo");
            headJson.put("name", "线路");
            returnJson.add(headJson);
            headJson.put("col", "companyName");
            headJson.put("name", "所属公司");
            returnJson.add(headJson);
        } else {
            headJson.put("col", "stopName");
            headJson.put("name", "站点名称");
            returnJson.add(headJson);
            headJson.put("col", "roadName");
            headJson.put("name", "所在街道");
            returnJson.add(headJson);
            headJson.put("col", "direction");
            headJson.put("name", "行向");
            returnJson.add(headJson);
        }

        headJson.put("col", "publishTime | limitTo : 10");
        headJson.put("name", "开始日期");
        returnJson.add(headJson);
        headJson.put("col", "publishOffTime | limitTo : 10");
        headJson.put("name", "截止日期");
        returnJson.add(headJson);
        // headJson.put("col","content");
        // headJson.put("name","内容");
        // returnJson.add(headJson);
        headJson.put("col", "makeBillDetailStatus | code : 'makeBillDetailStatus'");
        headJson.put("name", "上刊状态");
        returnJson.add(headJson);
        /*
         * headJson.put("col","makeBillImg"); headJson.put("name","上刊图片");
         * returnJson.add(headJson);
         */
        headJson.put("col", "");
        headJson.put("name", "操作");
        returnJson.add(headJson);

        return returnJson;
    }

    /**
     * 完工时间、图片处理
     * 
     * @param reqBean
     */
    public void completeBill(ReqBean reqBean) {
        String makeBillId = reqBean.getInfo("makeBillId");// 制作单id
        String publishDetailId = reqBean.getInfo("publishDetailId");// 上刊单明细id(对应到具体的资源)
        if (StringUtils.isBlank(makeBillId) || StringUtils.isBlank(publishDetailId)) {
            throw new BusinessException("缺少参数!");
        }

        // 插入完工照
        JSONArray imgsArray = JSONArray.fromObject(reqBean.getInfoObj("img"));
        JSONObject fileDataJson = mediaFileService.getDataJson(Constant.MEDIAFILE_SOURCE_TYPE_FINISHED,
                publishDetailId, imgsArray);
        if (imgsArray.size() > 0 && fileDataJson != null) {
            mediaFileService.addMediaFile(fileDataJson);
        }

        MakeBillDetail makeBillDetail = makeBillDetailMapper.selectByPrimaryKey(makeBillId, publishDetailId);
        makeBillDetail.setCompleteTime(DateUtil.parseDateTime(reqBean.getInfo("completeTime")));
        makeBillDetail.setStatus(Constant.PUBLISH_MAKE_BILL_DETAIL_STATUS_COMPLETED);
        makeBillDetail.setRemark(reqBean.getInfo("makeBillDetailRemark"));
        makeBillDetailMapper.updateByPrimaryKey(makeBillDetail);

    }
}